package util.file;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import util.LoggerUtils;


/**
 * Excel解析类，支持xls,xlsx
 */
public class ExcelReader {

  // 魔数，标识文件类型
  private static final int MAGIC_NUMBER_XLS = 0xD0CF11E0;
  private static final int MAGIC_NUMBER_XLSX = 0x504B0304;
  private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

  private Workbook workbook = null;

  /**
   * 构造方法：创建Excel解析类实例
   * 
   * @param filename 文件名
   */
  public ExcelReader(String filename) {
    try {
      init(new FileInputStream(new File(filename)));
    } catch (FileNotFoundException e) {
      LoggerUtils.errorInfo("通过输入流创建Excel解析类时出现异常!", e);
    }
  }

  /**
   * 构造方法：创建Excel解析类实例
   * 
   * @param file Excel文件对象
   */
  public ExcelReader(File file) {
    try {
      init(new FileInputStream(file));
    } catch (FileNotFoundException e) {
      LoggerUtils.errorInfo("通过输入流创建Excel解析类时出现异常!", e);
    }
  }

  /**
   * 构造方法：创建Excel解析类实例
   * 
   * @param is 输入流
   */
  public ExcelReader(InputStream is) {
    init(is);
  }

  private void init(InputStream is) {
    try (BufferedInputStream bis = new BufferedInputStream(is)) {
      bis.mark(0);
      byte[] mnbytes = new byte[4];
      bis.read(mnbytes);
      int magicNumber = 0;
      for (int i = 0; i < mnbytes.length; i++) {
        magicNumber |= (mnbytes[i] >= 0 ? mnbytes[i] : (256 + mnbytes[i])) << (8 * (4 - i - 1));
      }
      bis.reset();
      if (MAGIC_NUMBER_XLS == magicNumber)
        workbook = new HSSFWorkbook(bis);
      else if (MAGIC_NUMBER_XLSX == magicNumber)
        workbook = new XSSFWorkbook(bis);
      else
        throw new Exception("输入流不符合Excel格式!");
    } catch (Exception e) {
      LoggerUtils.errorInfo("创建Excel解析类时出现异常!", e);
    }
  }

  /**
   * 获取全部内容
   * 
   * @return
   * @throws Exception
   */
  public Map<Integer, List<Map<Integer, String>>> getDataByAll() throws Exception {
    Map<Integer, List<Map<Integer, String>>> sheetMap = new LinkedHashMap<Integer, List<Map<Integer, String>>>();
    int sheetCount = workbook.getNumberOfSheets();
    for (int i = 0; i < sheetCount; i++) {
      List<Map<Integer, String>> rowList = getDataBySheet(i);
      if (rowList != null && rowList.size() > 0) {
        sheetMap.put(i, rowList);
      }
    }
    return sheetMap;
  }

  /**
   * 获取指定sheet的内容
   * 
   * @param sheetNum sheet序号，从0开始
   * @return
   * @throws Exception
   */
  public List<Map<Integer, String>> getDataBySheet(int sheetNum) throws Exception {
    checkRange(sheetNum, -1, -1);

    List<Map<Integer, String>> rowList = new ArrayList<Map<Integer, String>>();
    Sheet sheet = workbook.getSheetAt(sheetNum); // 取得相应工作表
    int lastRowNum = sheet.getLastRowNum();
    // 遍历每一行
    for (int i = 0; i <= lastRowNum; i++) {
      Row row = sheet.getRow(i);
      if (row != null) {
        Map<Integer, String> cellMap = getDataByRow0(row);
        if (cellMap != null)
          rowList.add(cellMap);
      }
    }
    return rowList;
  }

  /**
   * 获取某个sheet中从指定行开始的数据（可用于去除标题行）
   * 
   * @param sheetNum sheet的序号，从0开始
   * @param startRowNum 开始行号，从0开始
   * @return
   * @throws Exception
   */
  public List<Map<Integer, String>> getDataBySheet(int sheetNum, int startRowNum) throws Exception {
    checkRange(sheetNum, startRowNum, -1);

    List<Map<Integer, String>> sheetList = new ArrayList<Map<Integer, String>>();
    Sheet sheet = workbook.getSheetAt(sheetNum);
    int lastRowNum = sheet.getLastRowNum();
    for (int i = startRowNum; i <= lastRowNum; i++) {
      Map<Integer, String> cellMap = getDataByRow0(sheet.getRow(i));
      if (cellMap != null)
        sheetList.add(cellMap);
    }
    return sheetList;
  }

  /**
   * 获取某一sheet中指定行的数据
   * 
   * @param sheetNum sheet的序号，从0开始
   * @param startRowNum 行号，从0开始
   * @return
   * @throws Exception
   */
  public Map<Integer, String> getDataByRow(int sheetNum, int rowNum) throws Exception {
    checkRange(sheetNum, rowNum, -1);

    Sheet sheet = workbook.getSheetAt(sheetNum);
    return getDataByRow0(sheet.getRow(rowNum));
  }

  /**
   * 获取某个sheet中某一列的数据
   * 
   * @param sheetNum sheet的序号，从0开始
   * @param columnNum 列序号，从0开始
   * @return
   * @throws Exception
   */
  public Map<Integer, String> getDataByColumn(int sheetNum, int columnNum) throws Exception {
    Sheet sheet = workbook.getSheetAt(sheetNum);
    int lastRowNum = sheet.getLastRowNum();
    Map<Integer, String> cellMap = new LinkedHashMap<Integer, String>();
    for (int i = 0; i <= lastRowNum; i++) {
      Row row = sheet.getRow(i);
      Cell cell = row.getCell(columnNum);
      cellMap.put((int) i, getCellValue(cell));
    }
    return trimMap(cellMap);
  }

  /**
   * 获取指定单元格的数据
   * 
   * @param sheetNum sheet序号，从0开始
   * @param rowNum 行号，从0开始
   * @param cellNum 列号，从0开始
   * @return
   * @throws Exception
   */
  public String getDataByCell(int sheetNum, int rowNum, int cellNum) throws Exception {
    checkRange(sheetNum, rowNum, cellNum);

    Sheet sheet = workbook.getSheetAt(sheetNum);
    Row row = sheet.getRow(rowNum);
    return getCellValue(row.getCell(cellNum));
  }

  /**
   * 获取当前sheet中合并单元格的个数
   * 
   * @param sheet
   * @return
   */
  public Integer getMergeCount(Sheet sheet) {
    return sheet.getNumMergedRegions();
  }

  /**
   * 获取合并单元格的值
   * 
   * @param sheet 当前sheet对象
   * @param rowNum 获取值的行数
   * @param cellNum 获取值的列数
   * @return
   */
  public String getMergedRegionValue(Sheet sheet, int rowNum, int cellNum) {
    // 获取合并单元格的总个数
    int sheetMergeCount = getMergeCount(sheet);
    for (int i = 0; i < sheetMergeCount; i++) {
      CellRangeAddress ca = sheet.getMergedRegion(i);
      int firstColumn = ca.getFirstColumn();
      int lastColumn = ca.getLastColumn();
      int firstRow = ca.getFirstRow();
      int lastRow = ca.getLastRow();
      if (rowNum >= firstRow && rowNum <= lastRow) {
        if (cellNum >= firstColumn && cellNum <= lastColumn) {
          Row row = sheet.getRow(rowNum);
          Cell cell = row.getCell(cellNum);
          return getCellValue(cell);
        }
      }
    }
    return null;
  }

  /**
   * 获取某一行的数据
   * 
   * @param row Excel行对象
   * @return
   * @throws Exception
   */
  private Map<Integer, String> getDataByRow0(Row row) throws Exception {
    Map<Integer, String> cellMap = new LinkedHashMap<Integer, String>();
    int lastCellNum = row.getLastCellNum();
    // 遍历每一列
    for (int c = 0; c < lastCellNum; c++) {
      Cell cell = row.getCell(c);
      cellMap.put(c, StringUtils.trim(getCellValue(cell)));
    }
    return trimMap(cellMap);
  }

  /**
   * 获取单元格的值
   * 
   * @param cell
   * @return
   */
  private String getCellValue(Cell cell) {
    String value = "";
    if (null != cell) {
      try {
        switch (cell.getCellType()) {
          case Cell.CELL_TYPE_NUMERIC: // 数字&日期
            if (DateUtil.isCellDateFormatted(cell)) {
              value = sdf.format(cell.getDateCellValue());
            } else {
              value = String.valueOf(cell.getNumericCellValue());
            }
            break;
          case Cell.CELL_TYPE_STRING: // 字符串
            value = cell.getStringCellValue();
            break;
          case Cell.CELL_TYPE_BOOLEAN: // Boolean
            value = String.valueOf(cell.getBooleanCellValue());
            break;
          case Cell.CELL_TYPE_FORMULA: // 公式
            switch (cell.getCachedFormulaResultType()) {
              case Cell.CELL_TYPE_NUMERIC: // 数字&日期
                if (DateUtil.isCellDateFormatted(cell)) {
                  value = sdf.format(cell.getDateCellValue());
                } else {
                  value = String.valueOf(cell.getNumericCellValue());
                }
                break;
              case Cell.CELL_TYPE_STRING: // 字符串
                value = cell.getStringCellValue();
                break;
              case Cell.CELL_TYPE_BOOLEAN: // Boolean
                value = String.valueOf(cell.getBooleanCellValue());
                break;
              case Cell.CELL_TYPE_BLANK: // 空值
                break;
              case Cell.CELL_TYPE_ERROR: // 故障
                break;
              default:
                break;
            }
            break;
          case Cell.CELL_TYPE_BLANK: // 空值
            break;
          case Cell.CELL_TYPE_ERROR: // 故障
            break;
          default:
            break;
        }
      } catch (Exception e) {
        value = cell.toString();
      }
    }
    return value;
  }

  /**
   * 去除无效的行（没有任何数据的行）
   * 
   * @param params
   * @return
   */
  private Map<Integer, String> trimMap(Map<Integer, String> params) {
    if (params != null && params.size() > 0) {
      Iterator<Integer> iterator = params.keySet().iterator();
      while (iterator.hasNext()) {
        String value = params.get(Integer.valueOf(iterator.next()));
        if (StringUtils.isNotBlank(value)) {
          return params;// 只要一行中有一个单元格有值，就说明这一行数据有用
        }
      }
    }
    return null;
  }

  /**
   * 检查指定位置是否越界
   * 
   * @param sheetNum
   * @param rowNum
   * @param cellNum
   * @throws Exception
   */
  private void checkRange(int sheetNum, int rowNum, int cellNum) throws Exception {
    if (sheetNum >= 0) {
      int sheetCount = workbook.getNumberOfSheets();
      if (sheetNum > sheetCount - 1)
        throw new Exception("无效的sheet序号" + sheetNum + "，最大sheet序号为：" + (sheetCount - 1));

      if (rowNum >= 0) {
        Sheet sheet = workbook.getSheetAt(sheetNum);
        int lastRowNum = sheet.getLastRowNum();
        if (rowNum > lastRowNum) {
          throw new Exception("无效的行号" + rowNum + "，当前sheet最大行号为：" + lastRowNum);
        }

        if (cellNum >= 0) {
          Row row = sheet.getRow(rowNum);
          int lastCellNum = row.getLastCellNum() - 1;
          if (cellNum > lastCellNum) {
            throw new Exception("无效的列号" + cellNum + "，当前行的最大列号为：" + lastCellNum);
          }
        }
      }
    }
  }

  public static void main(String[] args) throws Exception {
    FileInputStream fis = new FileInputStream(new File("C:\\Documents and Settings\\Administrator\\桌面\\notice.xls"));
    ExcelReader util = new ExcelReader(fis);

    // Map<Integer, String> map = util.getDataByRow(0, 0);
    // System.out.println(map);

    // List<Map<Integer, String>> list = util.getDataBySheet(0);
    // System.out.println(list);

    String data = util.getDataByCell(0, 0, 0);
    System.out.println(data);
  }
}
